// clean_bank_names.js
// 清理银行流水表中银行名称的多余空格

const SequelizeLib = require('sequelize');

// 数据库配置（使用项目中的配置）
const sequelize = new SequelizeLib({
  dialect: 'mysql',
  host: '127.0.0.1',
  port: 3306,
  username: 'root',
  password: '888mei525',
  database: 'lingxi_system',
  logging: false
});

// 银行流水模型
const BankTransactionModel = sequelize.define('bankTransaction', {
  id: {
    type: SequelizeLib.DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  organizationId: {
    type: SequelizeLib.DataTypes.INTEGER,
    allowNull: false,
    field: 'organization_id'
  },
  transactionId: {
    type: SequelizeLib.DataTypes.STRING(200),
    allowNull: false,
    unique: true,
    field: 'transaction_id'
  },
  payerName: {
    type: SequelizeLib.DataTypes.STRING(200),
    allowNull: false,
    field: 'payer_name'
  },
  payerAccount: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'payer_account'
  },
  amount: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2),
    allowNull: false
  },
  transactionDate: {
    type: SequelizeLib.DataTypes.DATE,
    allowNull: false,
    field: 'transaction_date'
  },
  transactionTime: {
    type: SequelizeLib.DataTypes.DATE,
    allowNull: false,
    field: 'transaction_time'
  },
  balance: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2)
  },
  remark: {
    type: SequelizeLib.DataTypes.TEXT
  },
  dataSource: {
    type: SequelizeLib.DataTypes.ENUM('file_import', 'plugin_capture'),
    defaultValue: 'file_import',
    field: 'data_source'
  },
  importBatch: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'import_batch'
  },
  isFiltered: {
    type: SequelizeLib.DataTypes.BOOLEAN,
    defaultValue: false,
    field: 'is_filtered'
  },
  filterReason: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'filter_reason'
  },
  createdAt: {
    type: SequelizeLib.DataTypes.DATE,
    field: 'created_at'
  },
  bankName: {
    type: SequelizeLib.DataTypes.STRING(200),
    field: 'bank_name'
  },
  currency: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  channel: {
    type: SequelizeLib.DataTypes.STRING(50)
  },
  fee: {
    type: SequelizeLib.DataTypes.DECIMAL(12, 2),
    defaultValue: 0
  },
  accountName: {
    type: SequelizeLib.DataTypes.STRING(200),
    field: 'account_name'
  },
  accountNumber: {
    type: SequelizeLib.DataTypes.STRING(100),
    field: 'account_number'
  }
}, {
  tableName: 'bank_transactions',
  timestamps: false
});

// 清理银行名称中的多余空格
async function cleanBankNames() {
  try {
    console.log('🔍 连接数据库...');
    await sequelize.authenticate();
    console.log('✅ 数据库连接成功!');
    
    // 查找所有银行名称包含空格的记录
    const transactions = await BankTransactionModel.findAll({
      where: {
        bankName: {
          [SequelizeLib.Op.like]: '% %'
        }
      }
    });
    
    console.log(`📊 找到 ${transactions.length} 条银行名称包含空格的记录`);
    
    let cleanedCount = 0;
    for (const transaction of transactions) {
      // 清理银行名称中的多余空格
      const cleanBankName = transaction.bankName.replace(/\s+/g, '');
      
      // 如果清理后的名称与原名称不同，则更新记录
      if (cleanBankName !== transaction.bankName) {
        await BankTransactionModel.update({
          bankName: cleanBankName
        }, {
          where: {
            id: transaction.id
          }
        });
        
        console.log(`  ✅ 已清理: "${transaction.bankName}" -> "${cleanBankName}"`);
        cleanedCount++;
      }
    }
    
    console.log(`\n✅ 完成清理，共处理 ${cleanedCount} 条记录`);
    
  } catch (error) {
    console.error('❌ 清理银行名称失败:', error.message);
    throw error;
  } finally {
    await sequelize.close();
  }
}

// 主函数
async function main() {
  try {
    console.log('🚀 开始清理银行名称中的多余空格...');
    await cleanBankNames();
    console.log('✅ 清理完成!');
  } catch (error) {
    console.error('❌ 清理失败:', error.message);
    process.exit(1);
  }
}

// 执行主函数
if (require.main === module) {
  main();
}

module.exports = {
  cleanBankNames
};